package BackUpAction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import Utils.Constant;
import Utils.DbAccess;

public class SplitNewsToTable{
	public static void main(String[] args) {
		DbAccess db = new DbAccess();
		DbAccess db2 = new DbAccess();
		int i=0; 
		try{
			db.connectTemp("localhost", Constant.DB);
			db2.connectTemp("localhost", Constant.DB);
			ArrayList<String> news_arr = new ArrayList<String>();
			String sql = "SELECT * FROM news WHERE DatePublished <= '2012-05-01 00:00:00' limit 5000";
			db.stmt = db.conn.prepareStatement(sql);
			db.rs = db.stmt.executeQuery();
			while(db.rs.next()){
				i++;
				System.out.println(i);
				String autoid = db.rs.getString("AutoId");
				String newsid = db.rs.getString("NewsId");
				news_arr.add(newsid);
//				String title = db.rs.getString("Title");
//				String summary = db.rs.getString("Summary");
//				String content = db.rs.getString("Content");
//				String thumbnail = db.rs.getString("Thumbnail");
//				String homepic = db.rs.getString("HomePic");
//				String picture = db.rs.getString("Picture");
//				String link = db.rs.getString("link");
//				String hadimg = db.rs.getString("HadImg");
//				String datepublished = db.rs.getString("DatePublished");
//				String dateviewing = db.rs.getString("DateViewing");
//				String source = db.rs.getString("Source");
//				String tempcat = db.rs.getString("TempCat");
//				String status = db.rs.getString("Status");
//				String type = db.rs.getString("Type");
//				String keyword = db.rs.getString("Keyword");
//				String totalnews = db.rs.getString("TotalNews");
//				String accesscount = db.rs.getString("AccessCount");
//				String ratelike = db.rs.getString("RateLike");
//				String poster = db.rs.getString("Poster");
//				String isnew = db.rs.getString("isnew");
//				if(insertNews(autoid, newsid, title, summary, 
//						content, thumbnail, homepic, picture, 
//						link, hadimg, datepublished, dateviewing, source, 
//						tempcat, status, type, keyword, totalnews, accesscount, 
//						ratelike, poster, isnew, db.conn)){	
//				
//					ArrayList<String> catid = getCat(newsid,db2.conn);
//					System.out.println(catid.size()+"   "+title);
//					for(int i=0; i < catid.size(); i++){
//						insertToNewsCatOld(newsid, catid.get(i), db2.conn);
//					}
//				}
			}
			new SplitNewsToTable().deleteInMain(news_arr, db);
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			db2.DBClose();
			db.DBClose();
		}
	}
	public static void insertToNewsCatOld(String newsid, String catid, Connection conn){
		PreparedStatement stmt = null;
		try{
			String sql = "INSERT INTO news_cat_old (NewsId,CatId) VALUES(?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsid);
			stmt.setString(2, catid);
			stmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(stmt!=null)try{stmt.close();}catch (Exception e) {}
		}
	}
	public static ArrayList<String> getCat(String newsid, Connection conn){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<String> arr = new ArrayList<String>();
		try{
			String sql = "SELECT * FROM news_cat WHERE NewsId=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, newsid);
			rs = stmt.executeQuery();
			while(rs.next()){
				arr.add(rs.getString("CatId"));
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null)try{rs.close();}catch (Exception e) {}
			if(stmt!=null)try{stmt.close();}catch (Exception e) {}
		}
		return arr;
	}
	public static boolean insertNews(String autoid, String newsid, String title, String summary, 
			String content, String thumbnail, String homepic, String picture,
			String link, String hadimg, String datepublished, String dateviewing, String source,
			String tempcat, String status, String type, String keyword, String totalnews,String accesscount,
			String ratelike, String poster, String isnew, 
			Connection conn){
		String sql = "INSERT INTO news_old (AutoId,NewsId,Title,Summary,Content,Thumbnail, HomePic,Picture," +
				"Link,HadImg,DatePublished,DateViewing,Source," +
				"TempCat,Status,Type,Keyword,TotalNews,AccessCount,RateLike," +
				"Poster,isnew) "
			+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, autoid);
			stmt.setString(2, newsid);
			stmt.setString(3, title);
			
			stmt.setString(4, summary);
			stmt.setString(5, content);
			stmt.setString(6, thumbnail);
			stmt.setString(7, homepic);
			stmt.setString(8, picture);
			stmt.setString(9, link);
			stmt.setString(10, hadimg);
			stmt.setString(11, datepublished);
			stmt.setString(12, dateviewing);
			stmt.setString(13, source);
			stmt.setString(14, tempcat);
			stmt.setString(15, status);
			stmt.setString(16, type);
			stmt.setString(17, keyword);
			stmt.setString(18, totalnews);
			stmt.setString(19, accesscount);
			stmt.setString(20, ratelike);
			stmt.setString(21, poster);
			stmt.setString(22, isnew);
			if(stmt.executeUpdate()>=1)
				return true;
			else return false;
		}catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			if(stmt!=null) try{stmt.close();}catch (Exception e) {}
		}
	}
	public boolean deleteInMain(ArrayList<String> arr,DbAccess db){
		String sql = "";
		try{
			for(int i=0;i<arr.size();i++){
				sql = "DELETE FROM news WHERE NewsId=?";
				db.stmt = db.conn.prepareStatement(sql);
				db.stmt.setString(1, arr.get(i));
				db.stmt.executeUpdate();
				db.stmt.close();
				sql = "DELETE FROM news_cat WHERE NewsId=?";
				db.stmt = db.conn.prepareStatement(sql);
				db.stmt.setString(1, arr.get(i));
				db.stmt.executeUpdate();
				db.stmt.close();
			}
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			
		}
		return true;
		
	}
}